<?php

/** Hi level sql routines.
 *
 *  This collection of functions (libary) is licensed under GPL2.
 *  See LICENSE or www.gnu.org for more details.
 *  @author Jyry Kuukkanen
 *  @copyright Jyry Kuukkanen
 *
 *  $Id: sosqlhi.php,v 1.20 2004/08/07 05:19:00 jyry Exp $
 */


/** Valid setSection methods */
define(SODA_RESET, 0);
define(SODA_REPLACE, 1);
define(SODA_ADD, 2);

/** Valid section types */
define(SODA_DATA, "d");
define(SODA_WHERE, "w");
define(SODA_WHERE_OPS, "p");    /** "<>", "=", ">=", "<=" */
define(SODA_WHERE_ORS, "r");    /** "OR" between conditions instead of "AND" */
define(SODA_ORDER, "o");
define(SODA_GROUP, "g");
define(SODA_BLOCK, "b");        /** Parenthesis */
define(SODA_PARENTHESES, "b");

/* Sort directions */
define(SODA_ASC, "asc");
define(SODA_DESC, "desc");

/* Actions */
define(SODA_SELECT, "SELECT");             /* select row(s) */
define(SODA_SELECT_DISTINCT, "DISTINCT");  /* select row(s) */
define(SODA_UPDATE, "UPDATE");             /* update row(s) */
define(SODA_INSERT, "INSERT");             /* insert row */
define(SODA_DELETE, "DELETE");             /* delete row(s) */
define(SODA_CREATE, "CREATE");             /* create table */

/* Options */
define(SODA_TRANSACT, "transact");         /* Use transactions */
define(SODA_DATE, "date");                 /* Date format */
define(SODA_TRIMMING, "trimming");         /* Use trim() */
define(SODA_INCASE, "incase");             /* Use incase() */
define(SODA_QUOTE, "quote");               /* Quote char: ' or " */

/* Valid db data types */
define(SODB_DATATYPES, "int dec date time datetime text");
define(SODT_INT, "int");
define(SODT_DEC, "dec");
define(SODT_DATE, "date");
define(SODT_TIME, "time");
define(SODT_DATETIME, "datetime");
define(SODT_TEXT, "text");
define(SODT_UNKNOWN, "");

/* Translation constants */
define(SODTL_APP2DB, "app");
define(SODTL_DB2APP, "db");
define(SODTL_TABLES, "tables");
define(SODTL_COLS, "cols");


/* Database translation table filename in options */
define(SODA_DBTT, "dbtt");

include_once("sobaseclass.php");
include_once("soarrayset.php");
include_once("soset.php");
include_once("sodatetime.php");
include_once("sostring.php");
include_once("sostreams.php");
include_once("sofiles.php");
include_once("sogeneric.php");
include_once("sodebug.php");
include_once("soconst.php");
include_once("DB.php");


/** soDa query result class.
 *  A class to hold return values from soDa query execution, if any. Also
 *  provides neat methods to access them.
 *  @var soArraySet $Values ColName as a key and an array of data for each
 *  @package sosqlhi
 */
class soDaResults extends soBaseClass {
    var $Values;

    /** Initialises object perperties.
     *  @param array/string $Cols Optional array of column names.
     *  @package sosqlhi
     */
    function soDaResults($Cols = array()) {
        $this->soBaseClass();
        $this->Values = new soArraySet();
        if ((is_array($Cols)) && (count($Cols))) {
            $this->setupCols($Cols);
        };
    } // soDaResults


    /** Sets up specified columns.
     *  Clear the current set and sets up a new with initial column names
     *      defined in array passed.
     *  @param array/string $Cols Column names to set up.
     *  @package sosqlhi
     */
    function setupCols($Cols) {
        $this->Values->clear();
        $this->Values->joinArrayAsKeys($Cols);
    } // setupCols


    /** Number of rows.
     *  Returns the number of rows in the result set or -1 if no columns are
     *      set up.
     *  @return int Returns row count or -1 (no cols).
     *  @package sosqlhi
     */
    function count() {
        $keys = $this->Values->getKeys();
        if (count($keys) == 0) {
            soDebug("soDaResults->count: no columns specified", SOD_LO);
            $this->Error = 1;
            return -1;
        } else {
            $this->Error = 0;
            return count($this->Values->getItem($keys[0]));
        };
    } // count


    /** Returns all values for specified column.
     *  @param string $ColName Column name to return.
     *  @return array/any Returns the whole column as a array. On error, when
     *      specified column does not exist, returns empty array and Error
     *      property is set to true
     *      (otherwise to false).
     *  @package sosqlhi
     */
    function getCol($ColName) {
        $ColName = soExtractVal($ColName, ":", 1);
        $result = $this->getCols($ColName);
        if ($this->Error) {
            return array();
        } else {
            return $result->getItem($ColName);
        };
    } // getCol


    /** Returns all values for specified columns.
     *  @param string $ColNames Column names as space delimited list to return.
     *      When left blank, returns all columns.
     *  @return array/string Returns the whole column as a array. On error,
     *      when some or none of the cols does not exists, sets the Error
     *      property to true;
     *  @package sosqlhi
     */
    function getCols($ColNames = NULL) {
        if (soIsNull($ColNames) || ($ColNames == "")) $ColNames = array();
        if (!is_array($ColNames)) $ColNames = soExplode($ColNames, " ");
        if (!count($ColNames)) $ColNames = $this->getColNames();

        $this->Error = 0;
        $result = new soArraySet();
        foreach ($ColNames as $col_name) {
            $col_name = soExtractVal($col_name, ":", 1);
            if ($this->Values->itemExists($col_name)) {
                $result->addItem($col_name, $this->Values->getItem($col_name));
            } else {
                $this->Error = $this->Values->Error;
            };
        }; // foreach

        return $result;
    } // getCols


    /** Returns a value for specified column and rowno.
     *  @param string $ColName Column name.
     *  @param int $RowNo Row number.
     *  @return string Returns a value for specified column and row. If column does not
     *      exist or row number is out of range, then a null string and sets
     *      Error property to true (otherwise to 0).
     *  @package sosqlhi
     */
    function getValue($ColName, $RowNo = 0) {
        return $this->Values->getArrayItem(soExtractVal($ColName, ":", 1), $RowNo);
    } // getValue


    /** Returns column names in the result set.
     *  @return array/string Column names in result set.
     */
    function getColNames() {
        return $this->Values->getKeys();
    } // getColNames;


    /** Returns a specified row from all return columns.
     *  NOTE! getResultRow() will be renamed to replace this function
     *  if no incompatibilities are found. --jq 20031216
     *  @param int $RowNo Rowno. to return values for.
     *  @param string $ColNames Space delimited list of column names to
     *      return or "" for all cols.
     *  @return array/string Returns a whole row as an array. Class property
     *      Error is set to true(1) on any error, such as when rowno. is out of
     *      range,
     *      otherwise to false(0).
     */
    function getRow($RowNo, $ColNames = "") {
        return $this->getResultRow($RowNo, $ColNames, 1);
    } // getRow


    /** Returns a row from result set.
     *  @param int $RowNo Rowno. to return values for.
     *  @param mixed $ColNames An array or space delimited list of column
     *      names to return or NULL for all cols.
     *  @param bool $AsNumArray When not set, returns associative array,
     *      otherwise returns an ordinal array.
     *  @return array/string Returns a whole row as an array. Class property
     *      Error is set to true(1) on any error, such as
     *      "rowno. out of range", otherwise it is set to false(0).
     */
    function getResultRow($RowNo, $ColNames = NULL, $AsNumArray = 0) {
        if ($ColNames) {
            if (!is_array($ColNames)) {
                $keys = soExplode($ColNames, " ");
            } else {
                $keys = $ColNames;
            }; // else if
        } else {
            $keys = $this->getColNames();
        }; // else if

        $kcount = count($keys);
        $result = array();

        if ($kcount == 0) {
            $this->Error = 1;
            return $result; // <-- EXIT
        };

        for ($k = 0; $k < $kcount; $k++) {
            ($AsNumArray) ? $key = $k : $key = $keys[$k];
            $result[$key] = $this->Values->getArrayItem($keys[$k], $RowNo);
        }; // for

        return $result; // <-- EXIT
    } // getResultRow


    /** Imports data from soSql object's result set.
     *  @param object $Data PEAR:DB object that holds the query results.
     *      values from SQL select query. Each array row contains an array of
     *      values for each specified columns.
     *  @param int $Append When set(1), append rows, otherwise clears each column
     *      first.
     *  @package sosqlhi
     */
    function importSoSqlData(&$Data, $Append = 0, $Count = 0, $Start = 0) {
        $keys = $this->Values->getKeys();
        $kcount = count($keys);
        
        /* Clear cols or get highest col */
        $old_start = 0;
        for ($k = 0; $k < $kcount; $k++) {
            if ($Append) {
                $old_start = max($old_start, 
                                 count($this->Values->getItem($keys[$k])) -1);
            } else {
                $this->Values->setItem($keys[$k], array());
            }; // else
        }; // for
        
        if (!$Data) return; // <-- EXIT - no rows found
        
        /* Import SQL resultset */
        $r_start = $old_start +$Start;
        if ($Count == 0) $Count = $Data->numRows();

        $r_end = $r_start +$Count;
        for ($k = 0; $k < $kcount; $k++) {
            $r = $r_start;
            $values = array();
            while (($r < $r_end) && 
                   ($row = $Data->fetchRow(DB_FETCHMODE_ORDERED, $r++))) {
                $values[] = StripSlashes($row[$k]);
            }; // 
            $values = array_merge($this->Values->getItem($keys[$k]), 
                                  $values);
            $this->Values->setItem($keys[$k], $values);
        }; // for $k
    } // importSoSqlData


}; // soDaResults



/** Create table column definition record
 *  @var string $Type Datatype
 *  @var bool $NotNull Set when null values are not allowed for this col
 *  @var int $Size String/numeric size
 *  @var mixed $Default Default value
 */
class soCreateColDef {
    var $Type;
    var $Size;
    var $NotNull;
    var $Default;
}; // soCreateCol


/** Table creation class
 *  @param string $__DbType Database type (pgsql, mysql...)
 *  @param array $__Cols Associative array of soCreateColDef objects where
 *      each key holds the column name.
 *  @param string $__TableName Table name
 *  @param array $__Types Associative array for general to native datatype
 *      names. The format is 
 *      array("generictype" => array("dbtype" => "value", ...), ...)
 */
class soCreateTable {
    
    var $__DbType;
    var $__Cols;
    var $__Types;

    /** Constructor
     *  @param string $DbType Database type name (pgsql, mysql...)
     */
    function soCreateTable($DbType, $TableName = "") {
        $this->__DbType = $DbType;
        $this->__clear();
        $this->__Types =
            array(
            "byte" => array("mysql" => "smallint")
            );
    } // soCreateTable


    /** Sets a column definition
     *  @param string $Name Col name to set
     *  @param string $Type Col datatype
     *  @param int $Size Col size
     *  @param bool $NotNull Not null value
     *  @param mized $Default Default col value
     *  @return False if name is not specified
     */
    function setCol($Name, $Type, $Size, $NotNull, $Default) {
        if (!$Name) return 0; // <-- EXIT
        
        $col = new soCreateColDef();
        $col->Type = $Type;
        $col->Size = $Size;
        $col->NotNull = $NotNull;
        $col->Default = $Default;

        $this->__Cols[$Name] = $col;
        
        return 1;
    } // setCol
    
    
    function setCols($Cols) {
        $result = 1;
        foreach ($Cols as $name => $col) {
            if ($this->setCol($name, $col["type"], $col["size"],
                              $col["notnull"], $col["default"])) $result = 0;
        }; // foreach
        
        return $result;
    } // setCols


    /** Returns native data type for column
     *  @param string $Type Datatype
     *  @return string Native type for the db or the input value when not
     *      found from types array.
     */
    function __getType($Type) {
        if ($types = $this->__Types[$Type]) {
            if (!$result = $types[$this->__DbType]) $result = $Type;
        } else {
            $result = $Type;
        }; // else if
        return $result;
    } // __getType


    /** Returns SQL clause to create a table
     *  Generates DbType specific create table clause.
     *  @param string $TableName Table name to get create clause for.
     *  @return string SQL create clause
     */
    function getSQL($TableName) {

        $result = "";
        foreach ($this->__Cols as $name => $col) {
            ($col->NotNull) ? $nn = "NOT NULL" : $nn = "";
            $result .= $name." ".$this->__getType($col->Type)." ".
                       $nn." ";
        }; // foreach

        return "CREATE TABLE ".$TableName." ".$result;
    } // getSQL


    /** Clear all defs
     */
    function clear() {
        $this->__Cols = array();
    } // clear


    /** Sets the table name
     */
    function setTable($TableName) {
        $this->__TableName = $TableName;
    } // clear


}; // soCreateTable


/** Class to handle quick table lookups.
 *  Example:
 *  $fetch = new soDbFetch(new soDa($dsn_string));
 *  $fetch->setTable("tablename");
 *  $fetch->setKey("key1:int key2", array(1, "foo"));
 *  $fetch->setCols("col1:int col2 col3");
 *  $row = $fetch->run(); // <-- returns an number array of values of 1st row
 *  $arrayset = $fetch->run(1); // <-- returns soArraySet object of all vals
 *
 *  @var soDa $__Soda Object to handle db queries
 *  @var soSet $Result List of retrieved columns and their values
 *  @package sosqlhi
 */
class soDbFetch {
    var $__Soda;
    var $Result;


    /** Initialises object.
     *  @param object soDa Data acces object
     *  @param int $LimitCount When set (> 0), limits the returned row count.
     */
    function soDbFetch(&$SodaObject, $LimitCount = 0) {
        $this->__Soda = $SodaObject;
        $this->init();
    } // soDbFetch


    /** Sets table name.
     *  @param string $Table Table name.
     *  @return bool Returns true(1) on success or false(0) when table name is
     *  empty.
     */
    function setTable($Table) {
        if (!$Table) return 0; // <-- EXIT
        $this->__Soda->setTable($Table, 1);
    } // setTable                               21


    /** Replaced by setKeyCols -- this will be removed
     *  @param mixed $KeyCols Key column name(s) as an array or a space
     *      delimited list. Each name in "col:datatype" format.
     *  @param array/any $KeyVals Key value(s).
     *  @return bool Returns true(1) on success or false(0) on error.
     */
    function setKey($KeyCols, $KeyVals) {
        return $this->setKeyCols(soJoinKeyValueArrays($KeyCols, $KeyVals));
    } // setKey


    /** Sets the key column names and values.
     *  @param array $KeyCols Key columns with values as an associative array
     *      array("col:datatype" => value, "col2:datatype" => value2)
     *  @return bool Returns true(1) on success or false(0) on error.
     */
    function setKeyCols($KeyCols) {
        list($key_cols, $key_vals) = soSplitAArray($KeyCols);
        return $this->__Soda->setSection(SODA_WHERE, $key_cols, $key_vals);
    } // setKeyCols


    /** Sets the sort column names and values.
     *  @param mixed $SortCols Sort column name(s) as an array or a space
     *      delimited list. Each name in "col:datatype" format.
     *  @param array/any $SortVals Sort values, "asc" or 1 for accending sort,
     *      other values for decending.
     *  @return bool Returns true(1) on success or false(0) on error.
     */
    function setSortCols($SortCols) {
        list($sort_cols, $sort_vals) = soSplitAArray($SortCols);
        return $this->__Soda->setSection(SODA_ORDER, $sort_cols, $sort_vals);
    } // setKey


    /** Sets column names to be retrieved.
     *  @param mixed $Cols Space delimited list or an array of column
     *      name(s), each name may be in "name:type" format.
     *  @return bool Returns true(1) on success or false(0) on error.
     *  @package sosqlhi
     */
    function setCols($Cols) {
        return $this->__Soda->setSection(SODA_DATA, $Cols, array());
    } // setCols


    /** Executes the query.
     *  @param bool $All When set, returns soArraySet, with all cols and
     *      their values. Otherwise returns an numeric array of the values
     *      of the 1st row found.
     *  @param string $Select Either SODA_SELECT or SODA_SELECT_DISTINCT
     *  @return array/string||soArraySet 1st returned row or all cols with
     *      values
     */
    function run($All = 0, $Select = SODA_SELECT) {
        $this->__Soda->setAction($Select, $LimitCount);
        if ($this->__Soda->run()) {
            if ($All) {
                $result = $this->__Soda->Results->getCols();
            } else {
                $result = $this->__Soda->Results->getRow(0);
            };
        } else {
            $result = NULL;
        };

        return $result;
    } // run


    /** Initialises class.
     *  This is separated from constuctor in order to reset the object
     *  without desroying and re-reating it.
     */
    function init() {
        $this->__Soda->clear();
        $this->Result = new soSet();
    } // init

}; // soDbFetch


/** Wrapper for soDbFetch
 *  For one-off sql queries: this uses soDbFetch but with simplified
 *  interface.
 *  @param string $Table Table name to query from.
 *  @param array/string $Keys Table key columns and their values. Each element
 *      holds a "colname:type=value" combination. Last param (value) may
 *      include what ever characters as everything after the "=" is considered
 *      as value. Type can be left empty: colname=value
 *  @param array||string $Cols Space delimited list or an array of of table
 *      columns to retrieve. Each may contain a "colname:datatype" pair, but
 *      the type is optional.
 *  @param soDa $SodaObject Database access object, if available, otherwise
 *      NULL
 *  @param string $Dsn Data source name, if soDa object is not available.
 *  @param string $Dsn Data source name, if soDa object is not available.
 *  @return array/string Values of return columns, each col == 1 value
 *  @package sosqlhi
 */
function soQuickDbFetch($Table, $Keys, $Cols,
                        $SodaObject = NULL, $Dsn = "", $Options = array()) {
    if ($SodaObject == NULL) $SodaObject = new soDa($Dsn, $Options);

    $fetch = new soDbFetch($SodaObject);
    $fetch->setTable($Table);
    $fetch->setKeyCols(soStrToAArray($Keys));
    $fetch->setCols($Cols);

    return $fetch->run();
}; // soQuickDbFetch


/** Database Access class.
 *  A class to access database in a structured manner - no SQL skills required,
 *  much.
 *  Usage ------------------------
 *
 * Update example:
 *    $soda = new soDa("pgsql://postgres@localhost/foobar");
 *    $soda->setAction(SODA_UPDATE);
 *    $soda->setTable("tajuton");
 *    $soda->setSection(SODA_DATA, "col1 col2:int col3", array("val1", 2, "BB"));
 *    $soda->setSection(SODA_WHERE, "col4 col5", array("n", "y"));
 *    $soda->setSection(SODA_WHERE_OPS, "col4 col2", array("<>", ""));
 *    $soda->setSection(SODA_WHERE_ORS, "col4", array(1));
 *    if (!$soda->run()) die("alone");
 *
 *  The above would result in executing following SQL clause:
 *  UPDATE tajuton SET col1="value1", col2=2, col3="three"
 *    WHERE col4<>"n" OR col2="y"
 *
 *  Select example (using existing soDa object)
 *    $soda->setAction(SODA_SELECT);
 *    $soda->clearSection(SODA_WHERE);
 *    $soda->setSection(SODA_DATA, "col1 col2:int", array());
 *    $soda->addToSection(SODA_DATA, "col3", array());
 *    $soda->setSection(SODA_ORDER, "col1 col3", array(0, 1));
 *    $soda->setSection(SODA_GROUP, "col1 col3", array());
 *    if (!$soda->run()) die("in peace");
 *
 *  The above would result in executing following SQL clause:
 *  SELECT col1, col2, col3
 *    FROM tajuton
 *    ORDER BY col1, col3 DESC
 *    GROUP BY col1, col3
 *
 *  - Action can be SODA_SELECT, SODA_UPDATE, SODA_INSERT, SODA_DELETE or
 *    SODA_CREATE
 *
 *  Notes on SODA_CREATE:
 *  - column names and datatypes are set through data col/values
 *  - "primary key" is presented with "*" as the
 *    column name in the data section and "(pri_col1, pri_col2)" as it's value.
 *
 *  Specialities:
 *  - In 'where' section, it is often required to use same column name more than
 *  onve with different operators, like 'somedate >= x and somedate < y'.
 *  soDa does not accept same column name in same same section more than once,
 *  so it is required to number these column and it done using
 *  'somedate#1 >= x and somedate#2 < y' - so any unique number after column name
 *  preceeded by '#' will do the trick.
 *  - Comparing columns, i.e. 'somecol = anothercol' is done using
 *  adding to or setting a section using the compared column name as a value
 *  preceeded by '$' sign: addToSection("somecol"', "\$anothercol:int". It is
 *  also *very* important to realise, that the datatype in this case must be
 *  nothinbg but 'int'. Always.
 *
 *
 * Create example:
 *  $soda = new soDa();
 *  $soda->setAction(SODA_CREATE);
 *  $soda->setTable = "tabletto";
 *  $soda->setSection(SODA_DATA, "clown pelle jester narri *",
 *                   array("int", "char(5)", "int", "date", "clown, pelle"));
 *  if (!$soda->run()) die("happy");
 *
 *  The above would result in executing following SQL clause:
 *  CREATE TABLE tabletto (
 *    clown int, pelle char(5), jester int, narri date,
 *    PRIMARY KEY(clown, pelle)
 *  )
 *
 * ----> An explanations for FinalCols and FinalVals properties:
 * These two properties are used when actually building the SQL clause.
 * The original sections remains untouched, but based on the driver and
 * other parameters, such as case-insensitive flag, required changes are
 * done to these two final properties of the section.
 *
 * e.g. PostgreSQL's select has case-insensitive search and it returns
 * values with trailing spaces. This causes grey hairs easily. In order to
 * over come these problems, Where in $Sections is left untouched and
 * copied to $FinalCols & Vals and then each column name is wrapped inside
 * "lower(trim(colname))" string. In some cases the column value requires
 * the same treatment. This way developer can see and access the original
 * values and the final ones. Otherwise it would be slighty difficult to
 * find "colname" in where column if it was renamed to
 * "lower(trim(colname))".
 *
 * Real life example:
 *     select col_a from table_x where col_b = 'value'
 * This would not necessary work, as the value for col_b could contain
 * trailing spaces. Instead, this would work:
 *     select col_a from table_x where trim(col_b) = trim('value')
 * Trimming the actual where value is not necessary, but doesn't do much
 * harm either.
 *
 *  @var soSQL $__SQL PEAR:DB object.
 *  @var int $__TransactionStaytus Transaction status - look
 *      getTransactStatus() for more details
 *  @var bool $__SelectTablePrefix When set, prefixes each select
 *      data column name with table name: table.column
 *  @var SODA_* $Action Action type: seen constants SODA_* for more.
 *  @var int $__LimitCount Max no. of rows to fetch with SELECT,
 *      0=all
 *  @var int $__LimitStart int First row no. to fetch with SELECT
 *  @var array/string $__Tables Table names
 *  @var array/string $__FinalTables Table names, translated to db names.
 *  @var array/SODA_* $__FinalSectionTypes Section types - initialised in
 *      constructor
 *  @var array/SODA_* $__SectionTypes Section types - initialised in
 *      constructor
 *  @var array/soSet $__Sections Data, where and order etc. section
 *      cols+values
 *  @var array/soSet $__FinalCols Final column names before xecution
 *  @var array/soSet $__FinalVals  Final column values before executing
 *  @var soDaResults $Results soDaResults Return values from select query
 *  @var soSet $__DataTypes List of data type for non-char columns:
 *      key=colname and value=data type. Data types
 *  @var string $CustomWhereSection User specified template for 'where' section
 *  @var string $Clause Actual sql clause to be run
 *  @var array/array $__Translations app-to-db and db-to-appa table and
 *      column name translations.
 *  @var string $__CacheFolder Cache folder
 *  @var int $__CacheTTL TTL of the next select to be run in secoonds.
 *  @var string $__DateStyle Date style used by db: "e"=European d/m/y, "j"=
 *  Japanese y/m/d or "u"=US m/d/y weirdness
 *  @var string $__TimeStyle Time style used by db.
 */
class soDa extends soBaseClass {

    /** @public */
    var $Action;
    var $Results;
    var $CustomWhereSection;
    var $Clause;

    /** @private */
    var $__SQL;
    var $__Options;
    var $__TransactionStatus;
    var $__SelectTablePrefix = 1;
    var $__LimitCount = 0;
    var $__LimitStart = 0;
    var $__Tables;
    var $__FinalSectionTypes;
    var $__SectionTypes;
    var $__Sections;
    var $__FinalCols;
    var $__FinalVals;
    var $__DataTypes;
    var $__Translations;
    var $__CacheFolder;
    var $__CacheTTL;
    var $__DateStyle = "j.40";
    var $__TimeStyle = "24:s--";


    /* This is how queries are construct from vars above
      SELECT:
        select $datacols from $table
               where $wherecolX $whereopX $wherevalueX
               $whereorx $wherecolY whereopY $wherevalueY
               order by $ordercols
               group by $groupcols
        ---> return values are stored in $this->Results
        ---> if $whereop[x] is empty, then "=" operator is used
        ---> if $whereor[x] is not set, then "and" is used
      INSERT
        insert into $table ($datacols) values ($datavalues)
      UPDATE
        update $table set $datacolx = $datavaluex
               $whereorx $wherecolY whereopY $wherevalueY
               order by $ordercols
      DELETE
        delete table
               $whereorx $wherecolY whereopY $wherevalueY
               order by $ordercols
      CREATE
        create table $table
               ($datacol0 $dataval0, ..., [primary key($datavalX)]
        --> If datacolX == "*", then it is considered primary key
         definition and datavalX contains a comma delimied list of
         primary key column names.
    */

    /** Constructor
     *  @param object PEAR:DB-object PEAR database connection object.
     *  @param array/string $Options Associative array of key/value pairs, for
     *  example array("trimming" => 1)
     *  @param string DB translation table file delimiter string.
     */
    function soDa(&$DbObj, $Options = array(), $TransDelim = "\n") {
        $this->soBaseClass();

        $this->__SQL = $DbObj;
        $this->__Options = $Options;

        $this->__Translations = array(SODTL_APP2DB, SODTL_DB2APP);

        /* Initialise section type arrays */
        $this->__FinalSectionTypes = array(SODA_DATA, SODA_WHERE, SODA_ORDER);
        $this->__SectionTypes = array_merge($this->__FinalSectionTypes,
                                            array(SODA_WHERE_OPS,
                                                  SODA_WHERE_ORS,
                                                  SODA_GROUP,
                                                  SODA_PARENTHESES));
        /* Setup sections */
        foreach ($this->__SectionTypes as $section) {
            $this->__Sections[$section] = new soSet();
        };

        /* Setup final sections */
        foreach ($this->__FinalSectionTypes as $section) {
            $this->__FinalCols[$section] = new soSet();
            $this->__FinalVals[$section] = new soSet();
        };

        $this->__DataTypes = new soSet();

        $this->Results = new soDaResults();
        
        if ($Options[SODA_DBTT]) {
            $this->setupTranslations($Options[SODA_DBTT], $TransDelim);
        }; // if
    } // constructor


    /** Sets up a cache folder for queries
     *  @param string $Folder Cache folder name (abs path)
     */
    function setCacheFolder($Folder) {
        $this->__CacheFolder = $Folder;
    } // setCacheFolder


    /** Sets up a cache time in seconds for currect select, if any
     *  Stores time-to-live value for the next SELECT to be run cache.
     *  When set to 0 (nought), queries directly from DB
     *  @param int $Secs Cache time in seconds
     */
    function setCacheTTL($Secs) {
        $this->__CacheTTL = $Secs;
    } // setCacheTime


    /** Flushes cache file(s)
     *  @param string $CacheID Cache ID to flush or "" for all files.
     */
    function flushCache($CacheID = "") {
        if ($CacheID) {
        } else {
        };
    } // flushCache


    /** Sets up translations for tables and columns.
     *  The idea behind translations is to provide a way to use same column
     *  names application wide even though different databases might have
     *  them called differently. For example Finnish people enjoy using
     *  Finnish names as column names, but Danish prefer they own language.
     *  To solve the problem, application uses whatever names for columns and
     *  when provided a Database Translation Table file, soDa can translate
     *  the column names back and forth so that application has no idea what
     *  the actual column names in the database are. Nor should the
     *  application even care about that.
     *  @param string $Translations Stream or stream name of table name
     *      column name translations.
     *  @param string $TransDelim Translation ini string delimiter.
     *  @package sosqlhi
     */
    function setupTranslations($Translations, $TransDelim = "\n") {
        $ini = new soIni($Translations, $TransDelim);

        $app2db = array();
        $db2app = array();

        /* Loop tables */
        $i = 0;
        $value = $ini->getValue(SODTL_TABLES, $i, 0);
        while (!$ini->Error) {
            $key = $ini->getValueName(SODTL_TABLES, $i);
            $app2db[$key] = $value;
            $db2app[$value] = $key;
            $value = $ini->getValue(SODTL_TABLES, ++$i, 0);
        }; // while

        /* Loop cols */
        $i = 0;
        $values = $ini->getValue(SODTL_COLS, $i, NULL);
        while (!$ini->Error) {
            $app_table = $ini->getValueName(SODTL_COLS, $i);
            $db_table = $app2db[$app_table];
            $keys = $values->getKeys();
            foreach ($keys as $key) {
                $value = $values->getItem($key);
                $app2db[$app_table.".".$key] = $db_table.".".$value;
                $db2app[$db_table.".".$value] = $app_table.".".$key;
            }; // foreach

            $values = $ini->getValue(SODTL_COLS, ++$i, NULL);
        }; // while

        $this->__Translations[SODTL_APP2DB] = $app2db;
        $this->__Translations[SODTL_DB2APP] = $db2app;
    } // setupTranslations


    /** Sets an action to perform.
     *  @param string $Action Action: SELECT, UPDATE, INSERT, DELETE or CREATE
     *  @param int $LimitCount Max no. of rows to SELECT
     *  @param int $LimitStart First row no. to return by SELECT
     */
    function setAction($Action, $LimitCount = 0, $LimitStart = 0) {
        $this->Action = $Action;
        $this->__LimitCount = $LimitCount;
        $this->__LimitStart = $LimitStart;
    } // setAction


    /** Returns current action
     */
    function getAction() {
        return $this->Action;
    } // getAction


    /** Returns a value of specified column and row.
     *  @param string $ColName Column name to access.
     *  @param string $RowNo Row no. to access.
     *  @param string $DefaultTableFix Prefixes $ColName with 1st table name
     *      when $ColName does not have table specified.
     *  @return int Returns the value of specified column and row. On error, when
     *      ColName is unknown or RowNo out of range, a nul string "" is
     *      returned and $this->Error is set to true(1).
     *  @package sosqlhi
     */
    function getDataVal($ColName, $RowNo = 0, $DefaultTableFix = 1) {
        $col_name = soExtractVal($ColName, ":", 1);
        if ((soStrPos($col_name, ".") == -1) && ($DefaultTableFix)) {
            $col_name = $this->__Tables[0].".".$col_name;
        }; // if

        if ((!$this->__Sections[SODA_DATA]->itemExists($col_name)) ||
            ($RowNo >= $this->Results->count())) {
            soDebug("soDa->getDataVal: error, RowNo=".$RowNo.", ".
                    "count=".$this->Results->count().", ".
                    "col_name=".$col_name.", ".
                    "exists=".
                    $this->__Sections[SODA_DATA]->itemExists($col_name),
                    SOD_DET);
            $this->Error = 1;
            return "";
        };

        $this->Error = 0;
        return $this->Results->getValue($col_name, $RowNo);
    } // getDataVal


    /** Checks wheter specified column is of type text or char.
     *  @param string $ColName Column name.
     *  @return int Returns true(1), if specified column is *not* of type int, float,
     *      time, date or datetime, but of type text or char (default, that is
     *      when not specified).
     *  @package sosqlhi
     */
    function isTextCol($ColName) {
        if ($ColName == "") return -1;

        $type = $this->getDataType($ColName);
        if (($type == "") || (!in_array($type,
                                        array(SODT_INT, SODT_DATE, SODT_DEC,
                                              SODT_TIME, SODT_DATETIME)))) {
            $result = 1;
        } else {
            $result = 0;
        };

        return $result;
    } // function isTextCol


    /** Adds driver specific quote symbols around text string.
     *  Modifies specified section so that depending on the driver used, all
     *  values are AddSlashes'd and then enclosed in single or double quotes.
     *  @param SODA_* $Section Section to modify.
     *  @package sosqlhi
     */
    function addQuotes($Section) {
        $keys = $this->__FinalVals[$Section]->getKeys();
        $kcount = count($keys);
        if ($kcount == 0) return; // <-- EXIT

        /* put a switch section here for other than pgsql */
        $quote = $this->__Options[SODA_QUOTE];
        if (!$quote) $quote = "'";

        /* loop through all values */
        for ($k = 0; $k < $kcount; $k++) {
            $value = $this->__FinalVals[$Section]->getItem($keys[$k]);

            $is_colname = (substr($value, 0, 1) == SO_DOLLAR);
            $is_text = $this->isTextCol($keys[$k]);
            $datatype = $this->getDataType($keys[$k]);
            $is_numeric = in_array($datatype, array(SODT_DATETIME, SODT_TIME,
                                                    SODT_DATE, SODT_INT,
                                                    SODT_DEC));
            $is_time = in_array($datatype, array(SODT_DATETIME, SODT_TIME,
                                                 SODT_DATE));
            if ($is_numeric && (soIsEmptyStr($value) || soIsNull($value))) {
                $this->__FinalVals[$Section]->setItem($keys[$k], "null");
            } else {
                /* The following block should be cleaned up */
                if (($is_text) || ($is_numeric)) {
                    if ((!$is_numeric) || ($is_time) ||
                        (($is_numeric) && ($value == "null"))) {
                        if (!soIsQuoted($value, $quote) && !$is_colname) {
                            $this->__FinalVals[$Section]->setItem($keys[$k],
                                            $quote.AddSlashes($value).$quote);
                        }; // if
                    }; // if !$is_numeric
                }; // if $is_text
                /* End of cleanup block */

            }; // null test
        }; // for $k

        return;
    } // addQuotes


    /** Trims trailing spaces from driver specified columns and values.
     *  Modifies specified section so that depending on driver and Trimming
     *  flag, all column names and/or values are put inside "lower(colname)" or
     *  similar.
     *  @param SODA_* $Section Section to modify.
     *  @param int $Target What to modify in section: 0 = modify name only,
     *      1 = modify value only or 2 = modify both.
     *  @return void Returns absolutely nothing. Nothing at all.
     *  @package sosqlhi
     */
    function addTrimming($Section, $Target = 0) {
        if (!$this->__Options[SODA_TRIMMING]) return; // <-- EXIT

        $keys = $this->__FinalCols[$Section]->getKeys();
        $kcount = count($keys);
        if ($kcount == 0) return; // <-- EXIT

        /* loop through all values */
        for ($k = 0; $k < $kcount; $k++) {
            if ($this->isTextCol($keys[$k])) {
                $o_value = $this->__Sections[$Section]->getItem($keys[$k]);
                $is_colname = (substr($o_value, 0, 1) == SO_DOLLAR);
                if (($Target != 1) && (!$is_colname)) {
                    $value = "trim(".$this->__FinalCols[$Section]->getItem($keys[$k]).")";
                    $this->__FinalCols[$Section]->setItem($keys[$k], $value);
                }; // if target != 1

                $value = $this->__FinalVals[$Section]->getItem($keys[$k]);
                if (($Target != 0) && ($o_value != "") && (!$is_colname)) {
                    $value = "trim(".$value.")";
                    $this->__FinalVals[$Section]->setItem($keys[$k], $value);
                }; // if target != 0

            } else {
                if ($Target != 0) {
                    $value = $this->__FinalVals[$Section]->getItem($keys[$k]);
                    if (soIsEmptyStr($value)) {
                        $this->__FinalVals[$Section]->setItem($keys[$k], "null");
                    };
                };
            } // else if textcol or datetime
        }; // for $i

        return;
    } // addTrimming


    /** Fixes columns and values for case-insensitivity.
     *  Modifies specified section so that depending on driver and Incase flag,
     *  all column names and/or values are put inside "lower(colname)" or
     *  similar.
     *  @param SODA_* $Section Section to modify.
     *  @param int $Target What to modify in section: 0 = modify name only,
     *      1 = modify value only or 2 = modify both.
     *  @return void Returns absolutely nothing. Nothing at all.
     *  @package sosqlhi
     */
    function addIncase($Section, $Target = 0) {
        if (!$this->__Options[SODA_INCASE]) return; // <-- EXIT

        $keys = $this->__FinalCols[$Section]->getKeys();
        $kcount = count($keys);
        if ($kcount == 0) return; // <-- EXIT

        /* loop through all values */
        for ($k = 0; $k < $kcount; $k++) {
            if ($this->isTextCol($keys[$k])) {
                $o_value = $this->__Sections[$Section]->getItem($keys[$k]);
                $is_colname = (substr($o_value, 0, 1) == SO_DOLLAR);
                if (($Target != 1) && (!$is_colname)) {
                    $value = "lower(".$this->__FinalCols[$Section]->getItem($keys[$k]).")";
                    $this->__FinalCols[$Section]->setItem($keys[$k], $value);
                }; // if target != 1

                $value = $this->__FinalVals[$Section]->getItem($keys[$k]);
                if (($Target != 0) && ($o_value != "") && (!$is_colname)) {
                    $value = "lower(".$value.")";
                    $this->__FinalVals[$Section]->setItem($keys[$k], $value);
                }; // if target != 0

            }; // isTextCol
        }; // for $i

        return;
    } // addIncase


    /** Assembles standard, not customised, sql query where section.
     */
    function getNormalWhereSection() {
        $result = "";

        $keys = $this->__FinalCols[SODA_WHERE]->getKeys();
        $kcount = count($keys);
        for ($k = 0; $k < $kcount; $k++) {
            $op = $this->__Sections[SODA_WHERE_OPS]->getItem($keys[$k]);
            $value = $this->__FinalVals[SODA_WHERE]->getItem($keys[$k]);
            if (($op == "") || ($op == "=")) {
                if (soIsNull(
                        $this->__Sections[SODA_WHERE]->getItem($keys[$k]))) {
                    $op = "is";
                    $value = "null";
                } else {
                    $op = "=";
                };
            };
            if (($k > 0) &&
                ($this->__Sections[SODA_WHERE_ORS]->getItem($keys[$k -1]))) {
                $glue = "or";
            } else {
                $glue = "and";
            };
            $colname = $this->__FinalCols[SODA_WHERE]->getItem($keys[$k]);
            $hashpos = soStrPos($colname, "#");

            $pars = soExplode(
                $this->__Sections[SODA_PARENTHESES]->getItem($keys[$k]), ",");
            $pars = array(intval($pars[0]), intval($pars[1]));
            if ($pars[0]) {
                $open = str_repeat("(", $pars[0]);
            } else {
                $open = "";
            }; // if
            if ($pars[1]) {
                $close = str_repeat(")", $pars[1]);
            } else {
                $close = "";
            }; // if

            if ($hashpos != -1) {
                $strlen = strlen($colname);
                $pos = $hashpos +1;
                while (($pos < $strlen) &&
                       (soIsNum(substr($colname, $pos, 1)))) $pos++;
                $colname = substr($colname, 0, $hashpos).
                           substr($colname, $pos);
            };
            $result = soJoinStr($result, " $glue ",
                                $open.$colname." ".$op." ".$value.$close);
        }; // for

        return $result;
    } // getNormalWhereSection


    /** Sets custom clause body
     *  @package sosqlhi
     */
    function setCustomWhereBody($Clause) {
        $this->CustomWhereSection = $Clause;
    } // setCustomWhereBody


    /** Order-by section.
     *  Assembles order-by section of the sql clause.
     *  @package sosqlhi
     */
    function getOrderSection() {
        $result = "";
        $keys = $this->__FinalCols[SODA_ORDER]->getKeys();
        foreach ($keys as $key) {
            $value = $this->__FinalVals[SODA_ORDER]->getItem($key);
            if ($value && ($value != "asc")) {
                $desc = "desc";
            } else {
                $desc = "";
            };
            $name = $this->__FinalCols[SODA_ORDER]->getItem($key);
            $result = soJoinStr($result, ", ", $name." ".$desc);
        }; // for

        if ($result) $result = " order by ".$result;

        return $result;
    } // getOrderSection


    /** Group-by section.
     *  Assembles group-by section of the sql clause.
     *  @package sosqlhi
     */
    function getGroupSection() {
        $result = "";
        $keys = $this->__Sections[SODA_GROUP]->getKeys();
        foreach ($keys as $key) {
            $result = soJoinStr($result, ", ", $key);
        }; // foreach

        if ($result) $result = " group by ".$result;

        return $result;
    } // getGroupSection


    /** Copies specified section values and columns to FinalCols and FinalVals.
     *  @param SODA_* $Section Section to copy.
     *  @package sosqlhi
     */
    function copyToFinals($Section) {

#        /* 1. remove table names in column names when not selecting from
#         * table(s) */
#        if (!in_array($this->Action, 
#            array(SODA_SELECT, SODA_SELECT_DISTINCT))) {
#            $keys = $this->__Sections[$Section]->getKeys();
#            foreach ($keys as $key) {
#                /* [0]=table, [1]=col name */
#                $split = soSplitStr($key, ".", 1);
#
#                if ($split != array()) {
#                    $this->__Sections[$Section]->renameItem($key, $split[1]);
#                };
#            }; // foreach
#        }; // if != select

        /* 2. perform the copy process */
        $this->__FinalVals[$Section] = $this->__Sections[$Section];
        $this->__FinalCols[$Section]->clear();
        $keys = $this->__Sections[$Section]->getKeys();
        $this->__FinalCols[$Section]->joinArray(
            soJoinKeyValueArrays($keys, $keys));
    } // copyToFinals


    /** Removes tables names from table.col array.
     *  This is used by run() method.
     *  @param array/string $ColNameArray Array of table.column names
     *  @return array/string Returns the table.column array without possible table part.
     *  @package sosqlhi
     */
    function __removeTableNames($ColNameArray) {
        $result = array();
        foreach ($ColNameArray as $col) {
            $split = soSplitStr($col, ".", 1);
            if (count($split)) {
                $result[] = $split[1];
            } else {
                $result[] = $col;
            }; // else if
        }; // foreach

        return $result;
    } // __removeTableNames


    /** Executes sql clause.
     *  @param int $DebugRun When set(1), assembles the clause, but doesn't
     *      actually run it. Great for debugging purposes and outdoor
     *      activities: "delete from live_table" oops....
     *  @return int Returns true(1) on success, false(0) on error.
     *  @package sosqlhi
     */
    function run($DebugRun = 0) {
        if (!$this->__SQL || (DB::isError($this->__SQL))) {
            return 0; // <-- EXIT
        }; // if

        /* Translate tables and column names */
        $this->__FinalTables = $this->__translate(SODTL_APP2DB,
                                                  $this->__Tables, 1);
        $this->Clause = "";

        $fcols = &$this->__FinalCols;
        $sects = array(SODA_DATA, SODA_WHERE, SODA_ORDER);
        foreach ($sects as $sect) {
            $this->copyToFinals($sect);
            $cols = $fcols[$sect]->getAsArray();
            $acols = array_keys($cols);
            $dcols = $this->__translate(SODTL_APP2DB, $acols, 1);
            for ($i = count($acols) -1; $i >= 0; $i--) {
                $cols[$acols[$i]] = $dcols[$i];
            }; // for
            $fcols[$sect]->joinArray($cols, 1);
        }; // foreach

        /* Loop data and where section and tune them in */
        $sections = array(SODA_DATA, SODA_WHERE);
        foreach ($sections as $section) {

            /* Convert date and datatime columns */
            $keys = $this->__FinalCols[$section]->getKeys();
            foreach($keys as $key) {

                $dtype = $this->getDatatype($key);
                if (in_array($dtype,
                             array(SODT_DATE, SODT_TIME, SODT_DATETIME))) {
                    $col = array($this->__FinalVals[$section]->getItem($key));
                    soConvertDates($col, $dtype,
                                   $this->__DateStyle,
                                   $this->__TimeStyle, 0, 1);
                    $this->__FinalVals[$section]->setItem($key, $col[0]);
                }; // if date/time
            }; // foreach key

            /* And then adjust quoting */
            if ($this->Action != SODA_CREATE) $this->addQuotes($section);
        }; // foreach section

        $this->addTrimming(SODA_WHERE, 2);
        $this->addTrimming(SODA_ORDER, 0);

        $this->addIncase(SODA_WHERE, 2);
        $this->addIncase(SODA_ORDER, 0);

        switch ($this->Action) {
            case SODA_INSERT:
                $this->addTrimming(SODA_DATA, 1);
                $vals = "";
                $keys = $this->__FinalCols[SODA_DATA]->getAsArray(1);
                $keys = $this->__removeTableNames($keys);
                $this->Clause = implode(", ", $keys);
                $vals = $this->__FinalVals[SODA_DATA];
                $this->Clause .= ") values (".$vals->toString(0, ", ")." ";
                $this->Clause = "into ".$this->__FinalTables[0].
                                " (".$this->Clause.")";
                break;

            case SODA_UPDATE:
                $cols = $this->__FinalCols[SODA_DATA]->getKeys();
                $keys = $this->__FinalCols[SODA_DATA]->getItems($cols);
                $keys = $this->__removeTableNames($keys);
                for ($i = count($keys) -1; $i >= 0; $i--) {
                    $this->Clause = soJoinStr($this->Clause, ", ",
                        $keys[$i]." = ".
                        $this->__FinalVals[SODA_DATA]->getItem($cols[$i]), 1);
                }; // for
                $this->Clause = $this->__FinalTables[0]." set ".$this->Clause;
                $this->Clause .= $this->getWhereSection();
                break;

            case SODA_DELETE:
                $this->Clause .= "from ";
                $this->Clause .= $this->__FinalTables[0];
                $this->Clause .= $this->getWhereSection();
                break;

            case SODA_CREATE:
                $prikey = "";
                $keys = $this->__FinalCols[SODA_DATA]->getKeys();
                $cols = $this->__removeTableNames($keys);
                $kcount = count($keys);
                for ($k = 0; $k < $kcount; $k++) {
                	$key = $keys[$k];
                	$col = $cols[$k];
                    if ($col == "*") {
                        $prikey =
                            $this->__FinalVals[SODA_DATA]->getItem($key);
                    } else {
                        $this->Clause = soJoinStr($this->Clause, ", ",
                            $col." ".
                            $this->__FinalVals[SODA_DATA]->getItem(
                                $key), 1);
                    };
                }; // foreach
                if ($prikey) $this->Clause .= ", primary key (".$prikey.")";

                $this->Clause = "table ".$this->__FinalTables[0].
                                " (".$this->Clause.")";
                break;

            case SODA_SELECT_DISTINCT:
            default: /* SODA_SELECT that is */
#                $this->__SQL->LimitCount = $this->__LimitCount;
#                $this->__SQL->LimitStart = $this->__LimitStart;
                $this->addTrimming(SODA_DATA, 0);
#                $keys = $this->__FinalCols[SODA_DATA]->getKeys();
                $cols = $this->__FinalCols[SODA_DATA]->getAsArray(0);
                if (!$this->__SelectTablePrefix) {
                    $cols = $this->__removeTableNames($cols);
                };
                $this->Clause = implode(", ", $cols);
                $this->Clause .= " from ".implode(", ", $this->__FinalTables);
                $this->Clause .= $this->getWhereSection();
                $this->Clause .= $this->getGroupSection();
                $this->Clause .= $this->getOrderSection();
                break;
        }; // switch

        $action = $this->Action;
        if ($action == SODA_SELECT_DISTINCT) $action = "SELECT DISTINCT";
        $this->Clause = $action." ".$this->Clause;

        if ($DebugRun) {
            $this->setError(0);
        } else {
            soDebug("soDa->run(before): '".$this->Clause."'", SOD_DET); flush();
#echo "run(before): '".$this->Clause."'<br>"; flush();
            $result = $this->__SQL->query($this->Clause);
            if (DB::isError($result)) {
                $this->setError(1, $result->getMessage()." / ".
                                $result->getUserInfo());
                soDebug("soDa->run: ERROR '".$this->ErrorMsg."'", SOD_HI);
            } else {
                $this->setError(0);
            }; // if
        }; // else if $DebugRun

        if (!$this->Error) {
            if (in_array($this->Action,
                     array(SODA_SELECT, SODA_SELECT_DISTINCT))) {
                soDebug("soDa->run: '".$this->Clause."' returned ".
                        $result->numRows()." rows", SOD_DET);

                $keys = $this->__Sections[SODA_DATA]->getKeys();
                $this->Results->setupCols($keys);
                $this->Results->importSoSqlData($result,
                                                0,
                                                $this->__LimitCount,
                                                $this->__LimitStart);
                foreach ($keys as $key) {
                    $dtype = $this->__DataTypes->getItem($key);
                    if (in_array($dtype,
                                 array(SODT_DATE, SODT_TIME, SODT_DATETIME))) {
                        $col = $this->Results->Values->getItem($key);
                        soConvertDates($col, $dtype,
                                       $this->__DateStyle,
                                       $this->__TimeStyle, 1, 1);
                        $this->Results->Values->setItem($key, $col);
                    }; // if time/date
                    $name = $this->__translate(SODTL_DB2APP, $key);
                    if ($name != $key) {
                        $this->Results->Values->renameItem($key, $name);
                    };
                }; // for $k
            } else {
                soDebug("soDa->run: '".$this->Clause."' OK", SOD_DET);
            }; // else if select
        }; // if

        return !$this->Error;
    } // run


    /** Returns transaction status
     *  @return int Transaction status as a interger value: -1 -- driver does not
     *  support transactions or last transaction command failed, 0 -- no
     *  transaction in progress, 1 -- transaction in progress.
     *  @package sosqlhi
     */
    function getTransactStatus() {
        if (!$this->getOption(SODA_TRANSACT)) {
            return -1;
        } else {
            return $this->__TransactStatus;
        };
    } // getTransactStatus
    
    
    /** Returns an option set in the constructor
     *  @param any $Name option name to return.
     *  @return any Option value.
     */
    function getOption($Name) {
        return $this->__Options[$Name];
    } // getOption
    

    /** Runs transaction start command
     *  Executes begin, commit or calcel
     *  @param string $Command "begin", "rollback" or "commit"
     *  @return bool Returns true(1) on success, otherwise false(0).
     */
    function runTransact($Command) {
        if (!$this->getOption(SODA_TRANSACT)) return 1;

        $Command  = strtoupper($Command);
        switch ($Command) {
            case "BEGIN":
                $this->__TransactStatus = 1;
                break;
            case "COMMIT":
            case "ROLLBACK":
                $this->__TransactStatus = 0;
                break;
            default: // unknown command
                $this->__TransactStatus = -1;
                return 0; // <-- EXIT
        }; // switch

        $this->__TransactStatus = !DB::isError($this->__SQL->query($Command));
        soDebug("soDa->runTransact($Command): '".
                $this->__TransactStatus."'", SOD_LO);
        return $this->__TransactStatus;
    } // runTransact


    /** Clears most of soDa settings.
     */
    function clear() {
        $this->clearAllSections();
        $this->__Tables = array();
        $this->Action = "";
    } // clear
    
    /** Stores a new table to the object,
     *  @param string $TableName Name of the table to store.
     *  @param bool $Clear When set, clears first all definitions. When table
     *      name is an empty string and clear flag is set, then all tables are
     *      removed.
     *  @package sosqlhi
     */
    function setTable($TableName, $Clear = 0) {
        if ($Clear) $this->__Tables = array();
        if ($TableName) {
            if (soArrayFind($this->__Tables, $TableName) == -1) {
                $this->__Tables[] = $TableName;
            }; // if
        }; // if
    } // setTable


    /** Sets several table name at once.
     *  @param array/string $Tables Table names.
     *  @param bool $Clear When set, clears all existing tables first.
     *  @return bool Returns true(1) on success or false(0) if any table name is empty.
     *  @package sosqlhi
     */
    function setTables($Tables, $Clear = 0) {
        if (!is_array($Tables)) return 0; // <-- EXIT
        if ($Clear) $this->__Tables = array();
        $result = 1;
        foreach ($Tables as $table) {
            if (!$this->setTable($table)) $result = 0;
        }; // foreach

        return $result;
    } // setTables


    /** Stores a datatype for specified column.
     *  When the query is run, a type "char" is used if no datatype is specified
     *  for certain column.
     *  @param string $ColName Column name.
     *  @param string $DataType Datatype (must be one in SODB_DATATYPE constant).
     *  @return int Returns true(1) if $DataType is correct, otherwise returns false(0).
     *  @package sosqlhi
     */
    function setDataType($ColName, $DataType) {
        $pos = soLocateVal(SODB_DATATYPES, " ", $DataType, 1);
        if (!$pos) {
            soDebug("soDa->setDataType: invalid type '$DataType' ".
                    "for col '$ColName'", SOD_DET);
            return 0; // <-- EXIT
        };

        if (soStrPos($ColName, ".") == -1) {
            $ColName = $this->__Tables[0].".".$ColName;
        };
        $this->__DataTypes->setitem($ColName, $DataType);

        return 1;
    } // setDataType


    /** Returns the datatype for specified column.
     *  @param string $ColName COlumn name
     *  @return SODT_* Datatype of the column.
     *  @package sosqlhi
     */
    function getDataType($ColName) {
        if (soStrPos($ColName, ".") == -1) {
            $ColName = $this->__Tables[0].".".$ColName;
        }; // if
        return $this->__DataTypes->getitem($ColName); // <-- EXIT
    } // getDataType


    /** Translates a table or column name app-to-db or vice-versa
     *  @param SODTL_APP2DB|SODTL_DB2APP $Direction From or to db
     *  @param string $Names An array or space delimited list of table or 
     *  column names to translate.
     *  @param bool $AsArray When set, returns translated names as an array
     *      instead of space delimited list.
     *  @return string Translated table or column name, or input col/table
     *      name when translation not found.
     */
    function __translate($Direction, $Names, $AsArray = 0) {
        if (!is_array($Names)) $Names = soExplode($Names, " ");
        $values = $this->__Translations[$Direction];
        $result = array();
        foreach ($Names as $name) {
            $db_name = &$values[$name];
            if (!$db_name) $db_name = $name;
            $result[] = $db_name;
        }; // foreach
        if ($AsArray) {
            return $result;
        } else {
            return implode(" ", $result);
        };
    } // translate


    /** Sets up a specified section with specified columns and values.
     *  @param SODA_* $Section Section to set up.
     *  @param array/string||string $ColNames An array or space delimited list
     *      of column:type pairs. Type is optional and defaults to char. If
     *      empty (""), then clears the section.
     *  >Values:array An array of values to assign to matching columns. Defaults
     *      to an empty that means, that it's filled with empty strings up to
     *      column count. This is used when, for example, setting up data
     *      columns names in 'select' statement that requires no data values.
     *  @param SODA_* $Method Can be SODA_RESET (clear before joining values),
     *      SODA_REPLACE (join by replacing existing columns) or SODA_ADD (join
     *      only non-existing columns).
     *  @return int Returns the number of columns add or -1 if ColNames has
     *      no column names.
     */
    function setSection($Section, $ColNames,
                        $Values = array(), $Method = SODA_RESET) {
        if (!is_array($ColNames)) $ColNames = soExplode($ColNames, " ");
        if (!count($ColNames)) return -1; // <-- EXIT

        $ccount = count($ColNames);
        if ($Values == NULL) $Values = array();
        if (!count($Values)) array_pad($Values, $ccount, "");

        if ($Method == SODA_RESET) {
            $Method = SODA_ADD;
            $this->__Sections[$Section]->clear();
        }; // if

        $result = 0;
        for ($c = 0; $c < $ccount; $c++) {
            $result += $this->addToSection($Section, $ColNames[$c],
                                           $Values[$c], $Method);
        }; // for $c

        return $result;
    } // setSection


    /** Adds cpecified column and value to specified section.
     *  @param SODA_* $Section Section to add the column and value to.
     *  @param string $ColName Column name.
     *  @param any $Value Column value.
     *  @param SODA_* $Method See setSection method for full list of options.
     *  @return int Returns the number of columns added to the section.
     *  Practically, this is always 1, unless $Method == SODA_ADD and $ColName
     *  is already in the section, when 0 is returned.
     */
    function addToSection($Section, $ColName, $Value, $Method = SODA_REPLACE) {
        $replace = 0;
        switch ($Method) {
            case SODA_REPLACE:
                $replace = 1;
                break;
            case SODA_RESET:
                $this->__Sections[$Section]->clear();
        }; // switch

        if (soStrPos($ColName, ":") == -1) $ColName .= ":";
        list($name, $type) = soSplitStr($ColName, ":", 1);
        if ($type == "dt") $type = "datetime";

        if (soStrPos($name, ".") == -1) $name = $this->__Tables[0].".".$name;
        if ($type) $this->setDataType($name, $type);
        return $this->__Sections[$Section]->setItem($name, $Value, $replace);
    } // addToSection


    /** Clear the specified section.
     *  @param SODA_* $Section Section to clear.
     *  @package sosqlhi
     */
    function clearSection($Section) {
        $this->__Sections[$Section]->clear();
    } // clearSection


    /** Clears all sections in soDa
     *  @package sosqlhi
     */
    function clearAllSections() {
        foreach ($this->__SectionTypes as $section) {
            $this->__Sections[$section]->clear();
        };

        foreach ($this->__FinalSectionTypes as $section) {
            $this->__FinalCols[$section]->clear();
            $this->__FinalVals[$section]->clear();
        };
    } // clearAllSections


    /** Sets up section columns with empty values.
     *  XXXXX IS THIS REALLY NEEDED ANYMORE?
     *  @param SODA_* $Section Section to set up with keys and empty values.
     *  @return int Returns the number of keys added.
     *  @package sosqlhi
     */
    function setSectionKeys($Section, $ColNames, $Method = SODA_RESET) {
        $cols = soExplodeStr($ColNames, " ");
        $ccount = count($cols);
        for ($c = 0; $c < $ccount; $c++) {
#            $cols[$c] = $this->__translate(SODTL_APP2DB, $cols[$c]);
        }; // for
        return $this->__Sections[$Section]->joinArrayAsKeys($cols);
    } // setSectionKeys


    /** Returns all the column names from all sections.
     *  @return array Returns all the column names as an array from where,
     *      order and data sections. Doubles are removed if any are found.
     *  @package sosqlhi
     */
    function getColNames() {
        $result = array();

        for ($i = 0; $i < 3; $i++) {
            switch ($i) {
                case 0:  $section = SODA_DATA;
                         break;
                case 1:  $section = SODA_WHERE;
                         break;
                default: $section = SODA_ORDER;
            }; // switch

            array_merge($result, $this->__Sections[$section]->getKeys());
        }; // for $i

        $ccount = count($result);
        for ($c = 0; $c < $ccount; $c++) {
            $result[$c] = $this->__translate(SODTL_DB2APP, $result[$c]);
        }; // for
        return $result;
    } // getColNames


    /** Assembles where-section of the sql clause.
     *  @package sosqlhi
     */
    function getWhereSection() {

        /* main of WhereSection */

        if ($this->CustomWhereSection) {
            $result = $this->CustomWhereSection;
        } else {
            $result = $this->getNormalWhereSection();
        };

        /* Replace
         * a) $ColX and $ValX with correct values from where section
         * b) Replace $table.colnames with proper table.colnames */
        $cols = $this->__FinalVals[SODA_WHERE];
        $keys = $cols->getKeys();
        foreach ($keys as $c) {
            $result = str_replace(SO_DOLLAR."Col".$c, $cols->getItem($c),
                                  $result);
            $result = str_replace(SO_DOLLAR."Val".$c, $cols->getItem($c),
                                  $result);
            $tmp_col = substr($cols->getItem($c), 1);
            $trans_col = $this->__translate(SODTL_APP2DB, $tmp_col);
            if ($tmp_col) {
                $result = str_replace(SO_DOLLAR.$tmp_col, $trans_col, $result);
            }; // if
        }; // for

        if ($result) $result = " where ".$result;
        return $result;
    } // getWhereSection


    /** Enable/disable table name before select column name.
     *  Then set, data column names in select clause will get
     *  prefixed with tablename: "table.column".
     *  @param bool $Value True or false.
     */
    function setSelectTablePrefix($Value = 1) {
        $this->__SelectTablePrefix = $value;
    } // setSelectTablePrefix


}; // soDa


/** Simple wrapper for PEARD:DB connect function.
 *  Prints an debug message when debugging enabled.
 *  @param string $Dsn Database source name.
 *  @return object PEAR::DB database object or PEAR::Error on error.
 */
function soDbConnect($Dsn) {
    $result = DB::connect($Dsn, true);
    if (DB::isError($result)) {
        soDebug("soDbConnect to '".$Dsn."' failed: ".
                $result->getMessage()."'", SOD_HI);
    } else {
        soDebug("soDbConnect to '".$Dsn."' ok", SOD_LO);
    }; // else if

    return $result;
} // soDbConnect


/** Disconnects from database
*  @param object $DbObj PEAR:DB database object.
*/
function soDbDisconnect($DbObj) {
    $dsn = soAddQuotes($DbObj->dsn["phptype"]."/".
                    $DbObj->dsn["database"], "'");
    if (($DbObj) &&  (!DB::isError($DbObj))) {
        $DbObj->disconnect();
        soDebug("soDbDisconneced from ".$dsn, SOD_LO);
    } else {
        soDebug("soDbDisconnect from ".$dsn." failed: '".
                $DbObj->getMessage()."'", SOD_HI);
    };
} // soDbDisconnect




/*
include("sodebug.inc");
$soDebugLevel = SOD_DET;

$conn = soDbConnect("pgsql://postgres@hiiri/template1");
if (DB::isError($conn)) return; // <-- EXIT

$x = new soDa($conn, array(SODA_TRIMMING => 1, SODA_INCASE => 1));
$x->setAction(SODA_SELECT);
$x->setTable("dzusers");
$x->setSection(SODA_DATA, "id surname", array());
$x->run();
echo $x->Results->Values->dump();

soDbDisconnect($conn);
*/

?>
